package com.mobi.Survey.model;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.mobi.Survey.controller.Pojo;
import com.mobi.Survey.controller.SurveyPojo;
import com.mobi.Survey.utils.Helper;

/**
 * 
 * <p>
 * <b>Author:</b> Yahya Arshad
 * <p>
 * <b>Company:</b> Mobi Media Soft
 * <p>
 * <b> Website:</b> www.mobimediasoft.com
 * <p>
 * SurveyTable.java This class provides interfaces for operation on Survey Table
 * 
 */
public class SurveyTable extends AbstractTable
{

	public static final String TABLE = "survey";

	public static final String COL_SURVEY_ID = "_Id";
	public static final String COL_SURVEY_KEY = "survey_key";
	public static final String COL_SURVEY_DESC = "description";
	public static final String COL_SUBS_ID = "subsid";
	public static final String COL_COMPANY_NAME = "company_name";
	public static final String COL_WEB_SITE = "web_site";
	public static final String COL_PHONE = "phone";
	public static final String COL_EMAIL = "email";
	public static final String COL_PAGE = "page";
	/**
	 * will be used for Information button as we don't have start button now(CFR
	 * - 4)
	 */
	public static final String COL_START_SURVEY_BTN_VAL = "txt_start_survey";
	public static final String COL_VIEW_CORRECT_ANSWER_BTN_VAL = "txt_correct_answer";
	public static final String COL_VIEW_RESULT_BTN_VAL = "txt_survey_result";
	public static final String COL_ICON_ADDRESS = "icon_url";
	public static final String COL_BG_COLOR = "bg_color";
	public static final String COL_FONT_COLOR = "font_color";
	public static final String COL_BG_IMAGE_URL = "bg_image_url";

	@Override
	public long insertRow(Pojo obj)
	{

		SurveyPojo survey = (SurveyPojo) obj;

		if (survey.getId() <= 0 || survey.getSurveyKey().length() == 0)
			return -1;
		SQLiteDatabase db = dbHelper.getWritableDatabase();

		ContentValues cv = new ContentValues();
		cv.put(COL_SURVEY_ID, survey.getId());
		cv.put(COL_SURVEY_KEY, survey.getSurveyKey().toUpperCase());
		cv.put(COL_SURVEY_DESC, survey.getSurveyDiscription());

		cv.put(COL_SUBS_ID, survey.getSubscriptionId());

		cv.put(COL_COMPANY_NAME, survey.getCompanyName());

		cv.put(COL_WEB_SITE, survey.getWebsite());
		cv.put(COL_PHONE, survey.getPhone());

		cv.put(COL_EMAIL, survey.getContactEmail());

		cv.put(COL_PAGE, survey.isShowAllControls());
		cv.put(COL_START_SURVEY_BTN_VAL, survey.getTxtButtonInformation());
		cv.put(COL_VIEW_CORRECT_ANSWER_BTN_VAL, survey.getTxtBtnCorrectAnswer());
		cv.put(COL_VIEW_RESULT_BTN_VAL, survey.getTxtBtnSurveyResult());
		cv.put(COL_ICON_ADDRESS, survey.getIconAddress());
		cv.put(COL_BG_COLOR, survey.getbGColor());
		cv.put(COL_BG_IMAGE_URL, survey.getbGImageUrl());
		cv.put(COL_FONT_COLOR, survey.getFontColor());

		if (db.insert(TABLE, null, cv) > 0)
			return survey.getId();
		else
		{
			return -1;
		}

	}

	public SurveyPojo getSurveyForAnswer(Context ctx, long surveyId)
	{

		SQLiteDatabase db = dbHelper.getReadableDatabase();

		Cursor cursor = db.query(TABLE, null, COL_SURVEY_ID + "=? ",
				new String[] { surveyId + "" }, null, null, null);
		SurveyPojo survey = new SurveyPojo();

		if (cursor != null && cursor.moveToFirst())
		{

			long id = cursor.getLong(0);
			String key = cursor.getString(1);
			String description = cursor.getString(2);
			int subsid = cursor.getInt(3);
			// String company = cursor.getString(4);
			// String website = cursor.getString(5);
			// String phone = cursor.getString(6);
			// String email = cursor.getString(7);
			boolean showAll = cursor.getInt(8) > 0;
			// String txtStartSurvey = cursor.getString(9);
			// String txtCorrectAnswer = cursor.getString(10);
			// String txtResultVals = cursor.getString(11);
			// String iconAddress = cursor.getString(12);
			String bgColor = cursor.getString(13);
			String bgImageUrl = cursor.getString(14);
			String fontColor = cursor.getString(15);
			survey.setId(id);

			// survey.setCompanyName(company);
			// survey.setContactEmail(email);
			survey.setShowAllControls(showAll);
			survey.setSubscriptionId(subsid);
			survey.setSurveyDiscription(description);
			survey.setSurveyKey(key);
			// survey.setWebsite(website);

			// survey.setPhone(phone);
			// survey.setTxtBtnStartSurvey(txtStartSurvey);
			// survey.setTxtBtnCorrectAnswer(txtCorrectAnswer);
			// survey.setTxtBtnSurveyResult(txtResultVals);
			survey.setbGColor(bgColor);
			survey.setbGImageUrl(bgImageUrl);
			survey.setFontColor(fontColor);

			SurveyDetailTable detailTable = new SurveyDetailTable(ctx);

			survey.setSurveyDetail(detailTable.getSurveyDetail(id));

			detailTable.closeDB();

		}

		return survey;

	}

	public SurveyPojo getSurveyWithDetail(Context ctx, long surveyId)
	{
		SQLiteDatabase db = dbHelper.getReadableDatabase();

		Cursor cursor = db.query(TABLE, null, COL_SURVEY_ID + "=? ",
				new String[] { surveyId + "" }, null, null, null);
		SurveyPojo survey = new SurveyPojo();

		if (cursor != null && cursor.moveToFirst())
		{

			long id = cursor.getLong(0);
			String key = cursor.getString(1);
			String description = cursor.getString(2);
			int subsid = cursor.getInt(3);
			// String company = cursor.getString(4);
			// String website = cursor.getString(5);
			// String phone = cursor.getString(6);
			// String email = cursor.getString(7);
			boolean isSingle = cursor.getInt(8) > 0;
			// String txtStartSurvey = cursor.getString(9);
			// String txtCorrectAnswer = cursor.getString(10);
			// String txtResultVals = cursor.getString(11);
			// String iconAddress = cursor.getString(12);
			String bgColor = cursor.getString(13);
			String bgImageUrl = cursor.getString(14);
			String fontColor = cursor.getString(15);
			survey.setId(id);

			// survey.setCompanyName(company);
			// survey.setContactEmail(email);
			survey.setShowAllControls(isSingle);
			survey.setSubscriptionId(subsid);
			survey.setSurveyDiscription(description);
			survey.setSurveyKey(key);
			// survey.setWebsite(website);

			// survey.setPhone(phone);
			// survey.setTxtBtnStartSurvey(txtStartSurvey);
			// survey.setTxtBtnCorrectAnswer(txtCorrectAnswer);
			// survey.setTxtBtnSurveyResult(txtResultVals);
			survey.setbGColor(bgColor);
			survey.setbGImageUrl(bgImageUrl);
			survey.setFontColor(fontColor);

			AbstractTable ab = new SurveyDetailTable(ctx);

			survey.setSurveyDetail(((SurveyDetailTable) ab).getSurveyDetail(id));

			ab.closeDB();

		}

		return survey;
	}

	public SurveyPojo getSurveyForInformationScreen(Context ctx, long surveyId)
	{

		SQLiteDatabase db = dbHelper.getReadableDatabase();

		Cursor cursor = db.query(TABLE, null, COL_SURVEY_ID + "=? ",
				new String[] { surveyId + "" }, null, null, null);
		SurveyPojo survey = new SurveyPojo();

		if (cursor != null && cursor.moveToFirst())
		{

			long id = cursor.getLong(0);
			String key = cursor.getString(1);
			String description = cursor.getString(2);
			int subsid = cursor.getInt(3);
			String company = cursor.getString(4);
			String website = cursor.getString(5);
			String phone = cursor.getString(6);
			String email = cursor.getString(7);
			// boolean isSingle = cursor.getInt(8) > 0;
			// String txtStartSurvey = cursor.getString(9);
			// String txtCorrectAnswer = cursor.getString(10);
			// String txtResultVals = cursor.getString(11);
			// String iconAddress = cursor.getString(12);
			// String bgColor = cursor.getString(13);
			// String bgImageUrl = cursor.getString(14);
			// String fontColor = cursor.getString(15);
			survey.setId(id);

			survey.setCompanyName(company);
			survey.setContactEmail(email);
			// survey.setShowAllControls(isSingle);
			survey.setSubscriptionId(subsid);
			survey.setSurveyDiscription(description);
			survey.setSurveyKey(key);
			survey.setWebsite(website);

			survey.setPhone(phone);
			SubscriptionTable subscriptionTable = new SubscriptionTable(ctx);
			survey.setSubscription(subscriptionTable.getSubscriberData(subsid));
			subscriptionTable.closeDB();
			// survey.setTxtBtnStartSurvey(txtStartSurvey);
			// survey.setTxtBtnCorrectAnswer(txtCorrectAnswer);
			// survey.setTxtBtnSurveyResult(txtResultVals);
			// survey.setbGColor(bgColor);
			// survey.setbGImageUrl(bgImageUrl);
			// survey.setFontColor(fontColor);

		}

		return survey;

	}

	public List<SurveyPojo> getSurveysWithSubscriptions(Context ctx)
	{

		List<SurveyPojo> surveys = new ArrayList<SurveyPojo>();

		SQLiteDatabase db = dbHelper.getReadableDatabase();

		Cursor cursor = db.query(TABLE, null, null, null, null, null, null);

		if (null != cursor && cursor.moveToFirst())
		{

			SubscriptionTable subscriptionTable = new SubscriptionTable(ctx);
			SurveyResultTable resultTable = new SurveyResultTable(ctx);
			do
			{
				SurveyPojo survey = new SurveyPojo();
				long id = cursor.getLong(0);
				String key = cursor.getString(1);
				String description = cursor.getString(2);
				int subsid = cursor.getInt(3);
				String company = cursor.getString(4);
				String website = cursor.getString(5);
				String phone = cursor.getString(6);
				String email = cursor.getString(7);
				boolean isSingle = cursor.getInt(8) > 0;
				String txtStartSurvey = cursor.getString(9);
				String txtCorrectAnswer = cursor.getString(10);
				String txtResultVals = cursor.getString(11);
				String iconAddress = cursor.getString(12);
				String bgColor = cursor.getString(13);
				String bgImageUrl = cursor.getString(14);
				String fontColor = cursor.getString(15);
				survey.setId(id);

				survey.setCompanyName(company);
				survey.setContactEmail(email);
				survey.setShowAllControls(isSingle);
				survey.setSubscriptionId(subsid);
				survey.setSurveyDiscription(description);
				survey.setSurveyKey(key);
				survey.setWebsite(website);

				survey.setPhone(phone);
				survey.settxtButtonInformation(txtStartSurvey);
				survey.setTxtBtnCorrectAnswer(txtCorrectAnswer);
				survey.setTxtBtnSurveyResult(txtResultVals);
				survey.setbGColor(bgColor);
				survey.setbGImageUrl(bgImageUrl);
				survey.setFontColor(fontColor);

				if (iconAddress != null && iconAddress.length() > 0)
				{

					String file = Helper.getPathToAppFolder()
							+ Helper.getHashNameFromUrl(iconAddress);

					survey.setBitmapIcon(Helper.decode70x70(file, 1));
				}

				survey.setSubscription(subscriptionTable
						.getSubscriberData(subsid));
				survey.setAnswered(resultTable.isNew(id) > 0);

				surveys.add(survey);

			} while (cursor.moveToNext());

			subscriptionTable.closeDB();
			resultTable.closeDB();
			cursor.close();
			cursor = null;
		}

		return surveys;

	}

	/**
	 * 
	 * 
	 * @param surveyKey
	 *            Survey Key
	 * @return id of the survey if found
	 */

	public int isNew(String surveyKey)
	{
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		Cursor cursor = db.query(TABLE, new String[] { COL_SURVEY_ID },
				COL_SURVEY_KEY + " Like '" + surveyKey + "' ", null, null,
				null, null);
		int id = -1;
		if (cursor != null)
		{

			if (!cursor.moveToFirst() && !(cursor.getCount() > 0))
			{

			} else
			{
				id = cursor.getInt(0);
			}

			cursor.close();
			cursor = null;
		}
		return id;

	}

	public SurveyTable()
	{

	}

	/**
	 * 
	 * @param sId
	 *            Survey Id
	 * @return Survey Key of given id
	 */
	public String getSurveyKeyById(long sId)
	{
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.query(TABLE, new String[] { COL_SURVEY_KEY },
				COL_SURVEY_ID + " = " + sId, null, null, null, null);

		String sKey = null;
		if (cursor != null)
		{

			if (cursor.moveToFirst() && (cursor.getCount() > 0))
			{

				sKey = cursor.getString(0);
			}

			cursor.close();
			cursor = null;
		}
		return sKey;
	}

	/**
	 * 
	 * @param sKey
	 *            Survey Key
	 * @return Survey id of geven key
	 */
	public int getSurveyIdByKey(String sKey)
	{
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.query(TABLE, new String[] { COL_SURVEY_ID },
				COL_SURVEY_KEY + " Like '" + sKey + "' ", null, null, null,
				null);

		int id = -1;
		if (cursor != null)
		{

			if (!cursor.moveToFirst() && !(cursor.getCount() > 0))
			{

			} else
			{
				id = cursor.getInt(0);
			}

			cursor.close();
			cursor = null;
		}
		return id;

	}

	public SurveyTable(Context ctx)
	{
		super(ctx);
	}

	/**
	 * 
	 * @return total surveys in database
	 */
	public int getSurveyCount()
	{
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.query(TABLE, new String[] { "COUNT(*) " }, null,
				null, null, null, null);
		int total = -1;
		if (cursor != null)
		{

			if (!cursor.moveToFirst() && !(cursor.getCount() > 0))
			{

			} else
			{
				total = cursor.getInt(0);
			}

			cursor.close();
			cursor = null;
		}
		return total;
	}

	@Override
	public boolean createTable(SQLiteDatabase sqliteDb)
	{

		String query = "create table " + TABLE + "( " + COL_SURVEY_ID
				+ " BIGINT primary key,  " + COL_SURVEY_KEY
				+ " text unique not null, " + COL_SURVEY_DESC + " text, "
				+ COL_SUBS_ID + " long, " + COL_COMPANY_NAME + " text, "
				+ COL_WEB_SITE + " text, " + COL_PHONE + " text, " + COL_EMAIL
				+ " text, " + COL_PAGE + " text, " + COL_START_SURVEY_BTN_VAL
				+ " text, " + COL_VIEW_CORRECT_ANSWER_BTN_VAL + " text, "
				+ COL_VIEW_RESULT_BTN_VAL + " text, " + COL_ICON_ADDRESS
				+ " text, " + COL_BG_COLOR + " text, " + COL_BG_IMAGE_URL
				+ " text, " + COL_FONT_COLOR + " text)";

		sqliteDb.execSQL(query);
		printMessage(query);
		return true;
	}

	/**
	 * 
	 * @param id
	 *            Id of Survey to be updated
	 * @param survey
	 *            Pojo of survey for update
	 * @return true if update successfull
	 */
	public boolean updateRow(long id, SurveyPojo survey)
	{
		SQLiteDatabase db = dbHelper.getWritableDatabase();

		ContentValues cv = new ContentValues();

		// cv.put(COL_SURVEY_KEY, survey.getSurveyKey());
		cv.put(COL_SURVEY_DESC, survey.getSurveyDiscription());

		cv.put(COL_SUBS_ID, survey.getSubscriptionId());

		cv.put(COL_COMPANY_NAME, survey.getCompanyName());

		cv.put(COL_WEB_SITE, survey.getWebsite());
		cv.put(COL_PHONE, survey.getPhone());

		cv.put(COL_EMAIL, survey.getContactEmail());

		cv.put(COL_PAGE, survey.isShowAllControls());
		cv.put(COL_START_SURVEY_BTN_VAL, survey.getTxtButtonInformation());
		cv.put(COL_VIEW_CORRECT_ANSWER_BTN_VAL, survey.getTxtBtnCorrectAnswer());
		cv.put(COL_VIEW_RESULT_BTN_VAL, survey.getTxtBtnSurveyResult());
		cv.put(COL_BG_COLOR, survey.getbGColor());
		cv.put(COL_BG_IMAGE_URL, survey.getbGImageUrl());
		cv.put(COL_FONT_COLOR, survey.getFontColor());
		int ret = db.update(TABLE, cv, COL_SURVEY_ID + " = ? ",
				new String[] { id + "" });
		cv.clear();

		return ret > 0;

	}

	@Override
	public boolean deleteRow(long id)
	{
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		int effected = db.delete(TABLE, COL_SURVEY_ID + " = ?",
				new String[] { id + "" });
		return effected > 0;
	}

	@Override
	public void deleteAllData()
	{
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.delete(TABLE, null, null);

	}

}
